﻿

CREATE FUNCTION [dbo].[resuma_CalculValoareSpor]
(
	-- Add the parameters for the function here
	@IdPersoanaSpor int
)
RETURNS decimal(18,2)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar decimal(18,2)
    DECLARE @BazaCalculSpor varchar(2)
    DECLARE @Format varchar(1)
    DECLARE @IDOcupatie int
    DECLARE @ProcentG decimal(6,2)
    DECLARE @Procent decimal(6,2)
    DECLARE @Valoare decimal(18,2)
    DECLARE @IDAngajatSalarizare int
    DECLARE @SalarCalculSpor decimal(18,2)
    DECLARE @IDTipSpor int
    DECLARE @Vechime decimal(18,3)
	DECLARE @dataAplicare datetime
	
	SELECT TOP 1 @dataAplicare = CAST(CAST(AnulCurent as varchar(4)) + '-' + CAST(LunaCurenta as varchar(2)) + '-01'  AS datetime) FROM ResUmaSetup a
	JOIN AngajatiSporuri d ON d.CodUnitate=a.CodUnitate
	WHERE d.IdPersoanaSpor = @IdPersoanaSpor
	
	SET @ResultVar=0
	SET @Valoare = 0
	-- Insert statements for procedure here
	SELECT @Procent = b.Procent,@BazaCalculSpor = BazaCalculSpor,@Format=Format,@Valoare=b.Valoare,@IDAngajatSalarizare = b.IDAngajatSalarizare
	,@IDTipSpor = a.IDTipSpor
	FROM [TipSpor] a 
	JOIN [AngajatiSporuri] b on a.IDTipSpor = b.IdTipSpor 
	WHERE Activ=1 AND b.IdPersoanaSpor = @IdPersoanaSpor
	AND @dataAplicare>=b.DeLaData AND
	(b.PanaLaData is null or b.PanaLaData>=@dataAplicare)
	
	
	
	
	IF @Format is null
		return @ResultVar
	
	IF @Format ='S' 
	BEGIN
		SET @ResultVar=@Valoare
		return @ResultVar
	END
	
	SELECT  @SalarCalculSpor = a.Salar,@Vechime = a.VechimeSporAni*12 + a.VechimeSporLuni FROM [AngajatiSalarizare] a WHERE a.IDAngajatSalarizare = @IDAngajatSalarizare
	
	IF @Format ='P' 
	BEGIN
		IF @BazaCalculSpor='SI'
			SET @ResultVar=ROUND(@Procent/100.00*@SalarCalculSpor,0)
		
		return @ResultVar
	END
	
	IF @Format ='G' 
	BEGIN
		SELECT @ProcentG=Valoare FROM  Grile gr
		WHERE gr.IDTipSpor = @IDTipSpor AND 
		@Vechime >= gr.Minim*12 AND @vechime < gr.Maxim*12
			
		IF @BazaCalculSpor='SI'	
			SET @ResultVar=ROUND(@ProcentG/100.00*@SalarCalculSpor,0)
		
		return @ResultVar
	END
	
	RETURN @ResultVar

END